package org.lhh.server.service.impl;

import org.apache.commons.lang3.StringUtils;
import org.lhh.server.common.api.JSONResult;
import org.lhh.server.common.utils.CollectionUtils;
import org.lhh.server.common.utils.IDUtils;
import org.lhh.server.config.web.exception.EtlException;
import org.lhh.server.config.web.exception.TestException;
import org.lhh.server.core.codec.database.DatabaseCodec;
import org.lhh.server.core.codec.trans.TransMetaCodec;
import org.lhh.server.core.repository.EtlRepository;
import org.lhh.server.emun.DatabaseNode;
import org.lhh.server.pojo.vo.DatabaseNodeVo;
import org.lhh.server.service.IDatabaseService;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.database.Database;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.database.DatabaseMetaInformation;
import org.pentaho.di.core.exception.KettleDatabaseException;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.row.ValueMetaInterface;
import org.pentaho.di.repository.LongObjectId;
import org.pentaho.di.trans.TransMeta;
import org.springframework.stereotype.Service;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Service
public class DatabaseServiceImpl implements IDatabaseService {

    @Override
    public void save(DatabaseCodec databaseCodec) {
        DatabaseMeta databaseMeta = DatabaseCodec.decode(databaseCodec);
        EtlRepository.saveDatabaseMeta(databaseMeta);
    }

    @Override
    public void test(DatabaseCodec databaseCodec) {
        DatabaseMeta databaseMeta = DatabaseCodec.decode(databaseCodec);
        final Database database = new Database(null, databaseMeta);
        try {
            database.connect();
        } catch (KettleDatabaseException e) {
            throw new TestException(e.getMessage());
        } finally {
            database.disconnect();
        }
    }


    @Override
    public DatabaseCodec getDataBaseById(Long id) {
        DatabaseMeta databaseMeta = EtlRepository.loadDatabaseMeta(new LongObjectId(id));
        return DatabaseCodec.encode(databaseMeta);
    }

    @Override
    public List<DatabaseNodeVo> explorer(String connection, String nodeId, String tableName) {
        try {
            DatabaseMeta databaseMeta = EtlRepository.loadDatabaseMeta(connection);
            if (StringUtils.isEmpty(nodeId)) {
                return Collections.singletonList(new DatabaseNodeVo(DatabaseNode.ROOT.getId(), DatabaseNode.ROOT.getpId(), databaseMeta.getName(), DatabaseNode.ROOT.getLabel(), 1, true));
            }
            if ("root".equals(nodeId)) {
                List<DatabaseNodeVo> rootChildren = new ArrayList<>();
                rootChildren.add(new DatabaseNodeVo(DatabaseNode.SCHEMA.getId(), DatabaseNode.SCHEMA.getpId(), DatabaseNode.SCHEMA.getLabel(), DatabaseNode.SCHEMA.getType(), 1, true));
                rootChildren.add(new DatabaseNodeVo(DatabaseNode.TABLE.getId(), DatabaseNode.TABLE.getpId(), DatabaseNode.TABLE.getLabel(), DatabaseNode.TABLE.getType(), 1, true));
                rootChildren.add(new DatabaseNodeVo(DatabaseNode.VIEW.getId(), DatabaseNode.VIEW.getpId(), DatabaseNode.VIEW.getLabel(), DatabaseNode.VIEW.getType(), 1, true));
                rootChildren.add(new DatabaseNodeVo(DatabaseNode.SYNONYM.getId(), DatabaseNode.SYNONYM.getpId(), DatabaseNode.SYNONYM.getLabel(), DatabaseNode.SYNONYM.getType(), 1, true));
                return rootChildren;
            } else {
                Database database = new Database(databaseMeta);
                try {
                    database.connect();
                    if ("schema".equals(nodeId) && databaseMeta.supportsSchemas()) {
                        DatabaseMetaData databaseMetaData = database.getDatabaseMetaData();
                        Map<String, String> connectionExtraOptions = databaseMeta.getExtraOptions();
                        if (databaseMetaData.supportsSchemasInTableDefinitions()) {
                            ArrayList<String> list = new ArrayList<>();
                            String schemaFilterKey = databaseMeta.getPluginId() + "." + DatabaseMetaInformation.FILTER_SCHEMA_LIST;
                            if ((connectionExtraOptions != null) && connectionExtraOptions.containsKey(schemaFilterKey)) {
                                String schemasFilterCommaList = connectionExtraOptions.get(schemaFilterKey);
                                String[] schemasFilterArray = schemasFilterCommaList.split(",");
                                for (String s : schemasFilterArray) {
                                    list.add(s.trim());
                                }
                            }
                            if (list.size() == 0) {
                                String sql = databaseMeta.getSQLListOfSchemas();
                                if (!Const.isEmpty(sql)) {
                                    Statement schemaStatement = database.getConnection().createStatement();
                                    ResultSet schemaResultSet = schemaStatement.executeQuery(sql);
                                    while (schemaResultSet != null && schemaResultSet.next()) {
                                        String schemaName = schemaResultSet.getString("name");
                                        list.add(schemaName);
                                    }
                                } else {
                                    ResultSet schemaResultSet = databaseMetaData.getSchemas();
                                    while (schemaResultSet != null && schemaResultSet.next()) {
                                        String schemaName = schemaResultSet.getString(1);
                                        list.add(schemaName);
                                    }
                                }
                            }
                            List<DatabaseNodeVo> schemaChildren = new ArrayList<>();
                            for (String schema : list) {
                                schemaChildren.add(new DatabaseNodeVo(IDUtils.getId(), DatabaseNode.SCHEMA.getId(), schema, "schemaTable", 1, false));
                            }
                            return schemaChildren;
                        }
                    } else if ("schemaTable".equals(nodeId)) {
                        DatabaseMetaData databaseMetaData = database.getDatabaseMetaData();
                        ResultSet resultSet = databaseMetaData.getTables(null, tableName, null, null);
                        List<DatabaseNodeVo> schemaTableChildren = new ArrayList<>();
                        while (resultSet.next()) {
                            String name = resultSet.getString(3);
                            if (!database.isSystemTable(name)) {
                                schemaTableChildren.add(new DatabaseNodeVo(IDUtils.getId(), DatabaseNode.SCHEMA.getId(), name, tableName, 2, false));
                            }
                        }
                        return schemaTableChildren;
                    } else if ("table".equals(nodeId)) {
                        Map<String, Collection<String>> tableMap = database.getTableMap();
                        List<String> tableKeys = new ArrayList<>(tableMap.keySet());
                        Collections.sort(tableKeys);
                        List<DatabaseNodeVo> tableChildren = new ArrayList<>();
                        for (String schema : tableKeys) {
                            List<String> tables = new ArrayList<String>(tableMap.get(schema));
                            Collections.sort(tables);
                            for (String table : tables)
                                tableChildren.add(new DatabaseNodeVo(IDUtils.getId(), DatabaseNode.TABLE.getId(), table, schema, 2, false));
                        }
                        return tableChildren;
                    } else if ("view".equals(nodeId)) {
                        Map<String, Collection<String>> viewMap = database.getViewMap();
                        List<String> viewKeys = new ArrayList<>(viewMap.keySet());
                        Collections.sort(viewKeys);
                        List<DatabaseNodeVo> viewChildren = new ArrayList<>();
                        for (String schema : viewKeys) {
                            List<String> views = new ArrayList<>(viewMap.get(schema));
                            Collections.sort(views);
                            for (String view : views)
                                viewChildren.add(new DatabaseNodeVo(IDUtils.getId(), DatabaseNode.VIEW.getId(), view, schema, 2, false));
                        }
                        return viewChildren;
                    } else if ("synonym".equals(nodeId)) {
                        Map<String, Collection<String>> synonymMap = database.getSynonymMap();
                        List<String> synonymKeys = new ArrayList<>(synonymMap.keySet());
                        Collections.sort(synonymKeys);
                        List<DatabaseNodeVo> synonymsChildren = new ArrayList<>();
                        for (String schema : synonymKeys) {
                            List<String> synonyms = new ArrayList<>(synonymMap.get(schema));
                            Collections.sort(synonyms);
                            for (String synonym : synonyms)
                                synonymsChildren.add(new DatabaseNodeVo(IDUtils.getId(), DatabaseNode.SYNONYM.getId(), synonym, schema, 2, false));
                        }
                        return synonymsChildren;
                    }
                    return Collections.emptyList();
                } catch (SQLException se) {
                    throw new EtlException(se.getMessage());
                } finally {
                    database.disconnect();
                }
            }
        } catch (KettleException ke) {
            throw new EtlException(ke.getMessage());
        }
    }

    @Override
    public List<String> tableFields(String graphXml, String connection, String schema, String table) {
        TransMeta transMeta = (TransMeta) TransMetaCodec.decode(graphXml);
        DatabaseMeta databaseMeta = transMeta.findDatabase(connection);
        Database database = new Database(databaseMeta);
        try {
            database.connect();
            List<String> fieldNames = new ArrayList<>();
            String schemaTable = databaseMeta.getQuotedSchemaTableCombination(transMeta.environmentSubstitute(schema), transMeta.environmentSubstitute(table));
            RowMetaInterface fields = database.getTableFields(schemaTable);
            if (fields != null) {
                for (int i = 0; i < fields.size(); i++) {
                    ValueMetaInterface field = fields.getValueMeta(i);
                    fieldNames.add(databaseMeta.quoteField(field.getName()));
                }
            }
            return fieldNames;
        } catch (KettleDatabaseException e) {
            throw new EtlException(e.getMessage());
        }
    }

    @Override
    public JSONResult getList() {
        final List<DatabaseMeta> databaseMetas = EtlRepository.readDatabases();
        if (!CollectionUtils.isEmpty(databaseMetas)) {
            List<DatabaseCodec> databaseCodecs = new ArrayList<>();
            for (DatabaseMeta databaseMeta : databaseMetas) {
                databaseCodecs.add(DatabaseCodec.encode(databaseMeta));
            }
            return JSONResult.ok(databaseCodecs);
        }
        return JSONResult.ok(Collections.emptyList());
    }

    @Override
    public void copy(DatabaseCodec databaseCodec) {
        DatabaseMeta databaseMeta = DatabaseCodec.decode(databaseCodec);
        databaseMeta.setObjectId(null);
        int i = 1;
        String name = databaseMeta.getName() + "-副本";
        while (EtlRepository.loadDatabaseMeta(name + i) != null) {
            i++;
        }
        databaseMeta.setName(name + i);
        EtlRepository.saveDatabaseMeta(databaseMeta);
    }

    @Override
    public String getSQL(TransMeta transMeta, String connection, String schema, String table, boolean yes) {
        DatabaseMeta inf = transMeta.findDatabase(connection);
        if (inf != null) {
            StringBuilder sql =
                    new StringBuilder("SELECT *"
                            + Const.CR + "FROM "
                            + inf.getQuotedSchemaTableCombination(schema, table) + Const.CR);
            if (yes) {
                Database db = new Database(null, inf);
                db.shareVariablesWith(transMeta);
                try {
                    db.connect();
                    RowMetaInterface fields = db.getQueryFields(sql.toString(), false);
                    if (fields != null) {
                        sql = new StringBuilder("SELECT" + Const.CR);
                        for (int i = 0; i < fields.size(); i++) {
                            ValueMetaInterface field = fields.getValueMeta(i);
                            if (i == 0) {
                                sql.append("  ");
                            } else {
                                sql.append(", ");
                            }
                            sql.append(inf.quoteField(field.getName())).append(Const.CR);
                        }
                        sql.append("FROM ").append(inf.getQuotedSchemaTableCombination(schema, table)).append(Const.CR);
                        return sql.toString();
                    } else {
                        throw new EtlException("Sorry, I couldn''t retrieve the fields"
                                + Const.CR + "Perhaps you don''t have the right permissions?");
                    }
                } catch (KettleException e) {
                    throw new EtlException("An error occurred\\"
                            + Const.CR + e.getMessage());
                } finally {
                    db.disconnect();
                }
            } else {
                return sql.toString();
            }
        } else {
            throw new EtlException("This database connection is no longer available\\");
        }
    }

    @Override
    public void remove(String name) {
        EtlRepository.deleteDatabaseMeta(name);
    }
}
